package com.studyroom.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.studyroom.entity.Order;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Result;
import java.util.List;
import java.util.Map;
import java.time.LocalDateTime;
import java.math.BigDecimal;

@Mapper
public interface OrderMapper extends BaseMapper<Order> {
    
    @Select("SELECT o.*, u.username " +
            "FROM orders o " +
            "LEFT JOIN users u ON o.user_id = u.id " +
            "ORDER BY o.created_at DESC LIMIT 5")
    List<Order> selectLatestOrders();

    @Select("SELECT o.id, o.user_id, o.total_amount, o.status, o.delivery_location, " +
           "DATE_FORMAT(o.delivery_time, '%Y-%m-%d %H:%i:%s') as delivery_time, " +
           "DATE_FORMAT(o.created_at, '%Y-%m-%d %H:%i:%s') as created_at, " +
           "DATE_FORMAT(o.updated_at, '%Y-%m-%d %H:%i:%s') as updated_at, " +
           "u.username, " +
           "GROUP_CONCAT(DISTINCT CONCAT(oi.id, ',', s.name, ',', oi.quantity, ',', oi.price)) as orderItems " +
           "FROM orders o " +
           "LEFT JOIN users u ON o.user_id = u.id " +
           "LEFT JOIN order_items oi ON o.id = oi.order_id " +
           "LEFT JOIN snacks s ON oi.snack_id = s.id " +
           "WHERE o.id = #{orderId} " +
           "GROUP BY o.id")
    Map<String, Object> getOrderDetail(@Param("orderId") Long orderId);

    @Select("SELECT COUNT(*) FROM orders WHERE DATE(created_at) = CURDATE()")
    Long countTodayOrders();

    @Select("SELECT COALESCE(SUM(total_amount), 0) FROM orders WHERE DATE(created_at) = CURDATE()")
    BigDecimal sumTodayIncome();

    @Select("WITH RECURSIVE dates AS (" +
            "  SELECT CURDATE() - INTERVAL 6 DAY as date " +
            "  UNION ALL " +
            "  SELECT date + INTERVAL 1 DAY " +
            "  FROM dates " +
            "  WHERE date < CURDATE()" +
            ") " +
            "SELECT DATE_FORMAT(d.date, '%m-%d') as date, " +
            "COALESCE(SUM(o.total_amount), 0) as value " +
            "FROM dates d " +
            "LEFT JOIN orders o ON DATE(o.created_at) = d.date " +
            "GROUP BY d.date " +
            "ORDER BY d.date")
    List<Map<String, Object>> selectIncomeStatistics();

    @Select("<script>" +
            "SELECT o.*, u.username, " +
            "DATE_FORMAT(o.created_at, '%Y-%m-%d %H:%i:%s') as createdAt, " +
            "DATE_FORMAT(o.updated_at, '%Y-%m-%d %H:%i:%s') as updatedAt, " +
            "DATE_FORMAT(o.delivery_time, '%Y-%m-%d %H:%i:%s') as deliveryTime " +
            "FROM orders o " +
            "LEFT JOIN users u ON o.user_id = u.id " +
            "WHERE 1=1 " +
            "<if test='keyword != null and keyword != \"\"'>" +
            "  AND (o.id LIKE CONCAT('%', #{keyword}, '%') " +
            "       OR u.username LIKE CONCAT('%', #{keyword}, '%') " +
            "       OR o.delivery_location LIKE CONCAT('%', #{keyword}, '%')) " +
            "</if>" +
            "<if test='status != null'>" +
            "  AND o.status = #{status} " +
            "</if>" +
            "<if test='startTime != null'>" +
            "  AND o.created_at &gt;= #{startTime} " +
            "</if>" +
            "<if test='endTime != null'>" +
            "  AND o.created_at &lt;= #{endTime} " +
            "</if>" +
            "<choose>" +
            "  <when test='sortField != null and sortOrder != null'>" +
            "    ORDER BY ${sortField} ${sortOrder}" +
            "  </when>" +
            "  <otherwise>" +
            "    ORDER BY o.created_at DESC" +
            "  </otherwise>" +
            "</choose>" +
            "</script>")
    @Results({
        @Result(column = "createdAt", property = "createdAt"),
        @Result(column = "updatedAt", property = "updatedAt"),
        @Result(column = "deliveryTime", property = "deliveryTime")
    })
    IPage<Order> searchOrders(
        Page<Order> page,
        @Param("keyword") String keyword,
        @Param("status") Integer status,
        @Param("startTime") LocalDateTime startTime,
        @Param("endTime") LocalDateTime endTime,
        @Param("sortField") String sortField,
        @Param("sortOrder") String sortOrder
    );

    @Select({"<script>",
            "SELECT o.*, DATE_FORMAT(o.created_at, '%Y-%m-%d %H:%i:%s') as createdAt, ",
            "DATE_FORMAT(o.updated_at, '%Y-%m-%d %H:%i:%s') as updatedAt ",
            "FROM orders o ",
            "WHERE o.user_id = #{userId} ",
            "<if test='status != null'> ",
            "  AND o.status = #{status} ",
            "</if> ",
            "ORDER BY o.created_at DESC",
            "</script>"})
    @Results({
        @Result(column = "createdAt", property = "createdAt"),
        @Result(column = "updatedAt", property = "updatedAt")
    })
    Page<Order> getMyOrders(Page<Order> page, @Param("userId") Long userId, @Param("status") Integer status);
} 